<?php

/**

 * Created by PhpStorm.

 * User: Administrator

 * Date: 2019/1/2

 * Time: 14:12

 */



namespace app\console\controller;

use app\console\model\OrderGoods;

use app\console\model\Order;

use think\Controller;

use think\Db;

use think\Request;

use think\Session;

use app\console\model\RefundOrderAudit;

use think\Cache;



class Catorder extends Controller

{

    protected $beforeActionList = [

        'shop_list'  =>[]

    ];



    public function shop_list()

    {

        $shop_list = Db::name('shop s')->field('id,shop_title')->select();

        $this->assign('shop_list', $shop_list);

    }

    /**

     * 财务管理

     */

    public function index($pa = 10)

    {



        $param = Request::instance()->param();

        $level = \session('role_id');//当前登录的管理员界别 5 7 8 9 10

        $page = Request::instance()->param('page',10);

        $where = [];

        $auding_status = Session::get('auding_status');

        if($auding_status == 1){

            $where['shop_id'] = ['eq',\session('shop_id')];

        }



        if (!empty($param['status'])){

            if($param['status'] == 2 || $param['status'] == 6){

                $where['o.status'] = ['in', $this->status];

            }else{

                $where['o.status'] = ['eq', $param['status']];

            }

        }





        if(!empty($param['status']) && $param['status'] ==  100){

            $where['o.status'] = ['in', '2,3,4,5,6'];

        }

        if (!empty($param['shop_id'])) $where['o.shop_id'] = ['eq', $param['shop_id']];

        if (!empty($param['finance_order'])) $where['o.finance_order'] = ['eq', $param['finance_order']];

        if (!empty($param['order_type'])) $where['o.order_type'] = ['eq', $param['order_type']];

        if (!empty($param['title'])){

            $order_goods  = Db::name('order_goods')->where('title','like', '%'.$param['title'].'%')->find();

            $gift = Db::name('order o')->join('gift g','o.gift_id = g.id')->where('g.title','like','%'.$param['title'].'%')->find();

            $single  = Db::name('single')->where('cat_name','like', '%'.$param['title'].'%')->find();

            if(empty($order_goods) && empty($gift)){

               // $where['m.username|o.shop_order_number'] = ['like', '%'.$param['title'].'%'];

            }

            $where['m.username|o.shop_order_number|og.title|g.title|single.cat_name|sc.title'] = ['like', '%'.$param['title'].'%'];

//            if(!empty($order_goods)){

//                $where['o.order_type'] = ['eq', 1];

//                $where['og.title|o.username'] = ['like', '%'.$param['title'].'%'];

//            }

//            if(!empty($gift)){

//                $where['o.order_type'] = ['eq', 3];

//                $where['g.title|o.username'] = ['like', '%'.$param['title'].'%'];

//            }



        }





        $end_time = time();

        if (!empty($param['interval_timer'])) {

            $interval_timer = explode('/', $param['interval_timer']);

            $end_time = strtotime($interval_timer[1]);

            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];

        }else{
			$six_begin_time = date('Y-m-d 00:00:00',time());
			$six_end_time = date('Y-m-d 23:59:59',time());
			$where['o.create_time'] = ['between', [$six_begin_time, $six_end_time]];
		}

        $total_price = Db::name('order')

            ->alias('o')

            ->join('member m', 'o.member_id = m.id','LEFT')

            ->join('shop s', 'o.shop_id = s.id','LEFT')

            ->join('order_goods og','o.id = og.order_id','LEFT')

            ->join('gift g','g.id = o.gift_id','LEFT')

            ->join('single','single.id = o.single_parent_id','LEFT')

            ->join('system_card sc','o.system_card_id = sc.id','left')

            ->field('o.*,m.username,s.shop_title')

            ->fetchSql(false)

            ->where($where)->sum('amount');

        $sj_total_price = Db::name('order')

            ->alias('o')

            ->join('member m', 'o.member_id = m.id','LEFT')

            ->join('shop s', 'o.shop_id = s.id','LEFT')

            ->join('order_goods og','o.id = og.order_id','LEFT')

            ->join('gift g','g.id = o.gift_id','LEFT')

            ->join('single','single.id = o.single_parent_id','LEFT')

            ->join('system_card sc','o.system_card_id = sc.id','left')

            ->field('o.*,m.username,s.shop_title')

            ->fetchSql(false)

            ->where($where)->sum('sj');

        $total_count = Db::name('order o')

            ->join('member m', 'o.member_id = m.id','LEFT')

            ->join('order_goods og','o.id = og.order_id','left')

            ->join('shop s', 'o.shop_id = s.id','left')

            ->join('gift g','g.id = o.gift_id','LEFT')

            ->join('single','single.id = o.single_parent_id','LEFT')

            ->join('system_card sc','o.system_card_id = sc.id','left')

            ->field('o.*,m.username,s.shop_title')

            ->where($where)->count();

        $d_price = 0;

        $c_price = 0;

        $in_array = [];



        $data = \app\console\model\Order::alias('o')

            ->join('order_goods og','o.id = og.order_id','left')

            ->join('member m', 'o.member_id = m.id','left')

            ->join('shop s', 'o.shop_id = s.id','left')

            ->join('gift g','g.id = o.gift_id','LEFT')

            ->join('single','single.id = o.single_parent_id','LEFT')

            ->join('system_card sc','o.system_card_id = sc.id','left')

            ->field('o.finance_order,o.order_online,o.system_card_id,o.t_type,o.gift_id,o.order_number,o.gift_id,o.shop_id,DATE_FORMAT(o.create_time,"%Y/%m/%d") create_time,o.id,o.order_type,o.single_parent_id,o.single_id,og.goods_id,o.amount,o.shop_order_number,o.username,o.status,o.refund_create_time,s.shop_title,o.shipping,o.is_read,o.order_number,s.shop_title')

            ->where($where)

            ->order('o.id','desc')

            ->paginate($pa, false, ['query' => $param])->each(function($item,$key) use($level,$end_time,&$d_price,&$c_price,&$in_array){

                if($item['status'] == 12 || $item['status'] == 10 || $item['status'] == 11){

                    $item->refund_create_time = Db::name('order_refund')->where('order_id',$item['id'])->value('create_time');

                }

                $item->year = explode('/',$item->create_time)[0];

                $item->month = explode('/',$item->create_time)[1];

                $item->pzrq = date('Y/n/j',$end_time);//凭证日期

                $item->kjnd = date('Y',$end_time);//会计年度

                $item->kjqj = date('n',$end_time);//会计期间

                $item->pzz = '记';//凭证字

                $item->pzh = '';//凭证号

                $item->d = 0;

                $item->c = '0';

                $item->pzzy = '';

                $item->subject_code = 0;

                $item->others = '';

                switch ($item->order_type){

                    case 1;

                        $dc = Db::name('shop_goods')->where('id',$item->goods_id)->value('dc');

                        $title = Db::name('shop_goods')->where('id',$item->goods_id)->value('title');

                        $item->pzzy = $title;

                        if($dc == 3){ //1借方 2贷方 为什么 这么判断呢？ 每次处理一个订单ID加进数组 订单有重复的时候?

                            $item->d = $item->amount;

                            if(!in_array($item->id,$in_array)){

                                $d_price += $item->amount;

                                $in_array[] = $item->id;

                            }

                        }else{

                            $item->c = $item->amount;

                            if(!in_array($item->id,$in_array)) {

                                $c_price += $item->amount;

                                $in_array[] = $item->id;

                            }

                        }

                        $item->subject_code = Db::name('shop_goods')->where('id',$item->goods_id)->value('code');

                        $item->others = 'ID:'.$item->goods_id.'-'.$title;

                        break;

                    case 2;

                        $dc = Db::name('single')->where('id',$item->single_id)->value('dc');

                        $parent_title = Db::name('single')->where('id',$item->single_parent_id)->value('cat_name');

                        $title = Db::name('single')->where('id',$item->single_id)->value('cat_name');

                        $item->pzzy = $title;

                        if($dc == 3){

                            $item->d = $item->amount;

                            $d_price += $item->amount;

                        }else{

                            $item->c = $item->amount;

                            $c_price += $item->amount;

                        }

                        $s_code1 = Db::name('single')->where('id',$item->single_parent_id)->value('code');

                        $s_code2 = Db::name('single')->where('id',$item->single_id)->value('code');

                        if($s_code1 && $s_code2){

                            $item->subject_code = $s_code1.'.'.$s_code2;

                        }else{

                            $item->subject_code  = '';

                        }



                        $item->others = $parent_title.'>'.$title;

                        break;

                    case 3;

                        if($item->t_type == 1){

                            $dc = Db::name('gift')->where('id',$item->gift_id)->value('dc');

                            $title = Db::name('gift')->where('id',$item->gift_id)->value('title');

                            $item->pzzy = $title;

                            if($dc == 3){

                                $item->d = $item->amount;

                                $d_price += $item->amount;

                            }else{

                                $item->c = $item->amount;

                                $c_price += $item->amount;

                            }

                            $item->subject_code = Db::name('gift')->where('id',$item->gift_id)->value('code');

                            $item->others = $title;

                        }

                        if ($item->t_type == 2) {

                            $dc = Db::name('system_card')->where('id', $item->system_card_id)->value('dc');

                            $title = Db::name('system_card')->where('id', $item->system_card_id)->value('title');

                            $item->pzzy = $title;

                            if ($dc == 3) {

                                $item->d = $item->amount;

                                $d_price += $item->amount;

                            } else {

                                $item->c = $item->amount;

                                $c_price += $item->amount;

                            }

                            $item->subject_code = Db::name('system_card')->where('id', $item->system_card_id)->value('code');

                            $item->others = $title;

                        }

                        break;

                }

                $item->subject_name = '';//科目名称

                $item->bbdm = 'RMB';//币别代码

                $item->bbmc = '人民币';//币别代码

                $item->amount = $item->amount;//原币金额

                $item->zd = '';//制单

                $item->sh = '';//审核

                $item->hz = 'NONE';//核准

                $item->cn = 'NONE';//出纳

                $item->jb = '';//经办

                $item->jsfs = '*';//结算方式

                $item->jsh = '';//结算号

                $item->sl = '0';//数量

                $item->sldw = '*';//数量单位

                $item->dj = '0';//单价

                $item->ckxx = '';//参考信息

                $item->ywrq = date('Y/n/j',$end_time);//业务日期  ....

                $item->wlyybh = '';//往来业务编号

                $item->ffs = 0;//附件数

                $item->xh = 1 ;//序号

                $item->xtmk = '';//系统模块

                $item->ywms = '';//业务描述

                $item->hl = 1;//汇率

                $item->flxh = $key+1;//分录序号

                $item->hsxm = '';//合算项目

                $item->gz = 0;//过账

                $item->jzpz = '';//机制凭证

                $item->xjll = '';//现金流量

            });



        if(isset($param['export'])){

            $header[0] = [

                'pzrq' => date('Y/n/j',$end_time),

                'kjnd' => date('Y',$end_time),

                'kjqj' => date('n',$end_time),

                'pzz' => '记',

                'pzh' => '',

                'subject_code' => '1001',

                'subject_name' => '',

                'bbdm' => 'RMB',

                'bbmc' => '人民币',

                'amount' => $total_price,

                'd' => $c_price,

                'c' => 0,

                'zd' => '',

                'sh' => '',

                'hz' => 'NONE',

                'cn' => 'NONE',

                'jb' => '',

                'jsfs' => '*',

                'jsh' => '',

                'pzzy' => '现金',

                'sl' => '0',

                'sldw' => '*',

                'dj' => 0,

                'ckxx' => '',

                'ywrq' => date('Y/n/j',$end_time),

                'wlyybh' => '',

                'ffs' => '0',

                'xh' => '1',

                'xtmk' => '',

                'ywms' => '',

                'hl' => '1',

                'flxh' => 0,

                'hsxm' => '',

                'gz' => '0',

                'jzpz' => '',

                'xjll' => '',

                'order_number' => '',

                'others' => ''

            ];

            //array_keys($header[0]);

            //$a=array("Volvo"=>"XC90","BMW"=>" ","Toyota"=>"Highlander");

            //print_r(array_keys($a));

            //halt(array_keys($header[0]));

            $array = array_merge($header,$data->toArray()['data']);

            //$this->push($array);

            //$this->exportExcel($array,'daochu',['amount']);

           $this->push($array);

        }



        return view('', ['data' => $data,

            'total_count' => $total_count,

            'total_price' => $total_price,

            'sj_total_price' => $sj_total_price,

        ]);

    }



    /**

     * 创建(导出)Excel数据表格

     * @param  array   $list        要导出的数组格式的数据

     * @param  string  $filename    导出的Excel表格数据表的文件名

     * @param  array   $indexKey    $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值)

     * @param  array   $startRow    第一条数据在Excel表格中起始行

     * @param  [bool]  $excel2007   是否生成Excel2007(.xlsx)以上兼容的数据表

     * 比如: $indexKey与$list数组对应关系如下:

     *     $indexKey = array('id','username','sex','age');

     *     $list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24));

     */

    public function exportExcel($list, $filename, $indexKey = ['id','order_number'], $startRow = 1, $excel2007 = false)

    {

        //文件引入

        //require_once APP_ROOT.'/Api/excel/PHPExcel.php';

        //require_once APP_ROOT.'/Api/excel/PHPExcel/Writer/Excel2007.php';

        require_once '../extend/phpoffice/phpexcel/Classes/PHPExcel.php';

        require_once '../extend/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php';



        if (empty($filename)) $filename = time();

        if (!is_array($indexKey)) return false;



        $header_arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');

        //初始化PHPExcel()

        $objPHPExcel = new \PHPExcel();



        //设置保存版本格式

        if ($excel2007) {

            $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

            $filename = $filename . '.xlsx';

        } else {

            $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);

            $filename = $filename . '.xls';

        }



        //接下来就是写数据到表格里面去

        $objActSheet = $objPHPExcel->getActiveSheet();

        //$startRow = 1;

        foreach ($list as $row) {

            foreach ($indexKey as $key => $value) {

                //这里是设置单元格的内容

                $objActSheet->setCellValue($header_arr[$key] . $startRow, $row[$value]);

            }

            $startRow++;

        }



        // 下载这个表格，在浏览器输出

        header("Pragma: public");

        header("Expires: 0");

        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");

        header("Content-Type:application/force-download");

        header("Content-Type:application/vnd.ms-execl");

        header("Content-Type:application/octet-stream");

        header("Content-Type:application/download");

        header('Content-Disposition:attachment;filename=' . $filename . '');

        header("Content-Transfer-Encoding:binary");

        $objWriter->save('php://output');

    }









    /**

     * @param int $pa

     * @return array|mixed|\think\response\View

     * 门店商品

     */

    public function goods($pa = 10)

    {

        $param = Request::instance()->param();

        $where = [];

        if (!empty($param['status'])){

            if($param['status'] == 2 || $param['status'] == 6){

                $where['o.status'] = ['in', $this->status];

            }else{

                $where['o.status'] = ['eq', $param['status']];

            }

        }

        if (!empty($param['interval_timer'])) {

            $interval_timer = explode('/', $param['interval_timer']);

            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];

        }

        if(!empty($param['shop_id'])) $where['o.shop_id'] = ['eq',$param['shop_id']];

        if(!empty($param['goods_id'])) $where['goods_id'] = ['eq',$param['goods_id']];

        if(!empty($param['title'])) $where['title|attribute'] = ['like','%'.$param['title'].'%'];

        $data = (new OrderGoods())

            ->alias('og')

            ->where($where)

            ->join('order o', 'og.order_id = o.id')

            ->join('shop s','o.shop_id = s.id')

            ->order('og.id','desc')

            ->paginate($pa, false, ['query' => $param]);

        $data = (new OrderGoods())

            ->alias('og')

            ->where($where)

            ->join('order o', 'og.order_id = o.id')

            ->join('shop s','o.shop_id = s.id')

            ->order('og.id','desc')

            ->field('og.*,s.*,o.*')

            ->paginate($pa, false, ['query' => $param]);

        $goods_list = OrderGoods::group('goods_id')->select();

        $total_count = (new OrderGoods())

            ->alias('og')

            ->where($where)

            ->join('order o', 'og.order_id = o.id')

            ->count();

        $t_data = (new OrderGoods())

            ->alias('og')

            ->where($where)

            ->join('order o', 'og.order_id = o.id')

            ->join('shop s','o.shop_id = s.id')

            ->order('og.id','desc')

            ->select();

        $total_price = 0;

        if(!empty($t_data)){

            foreach ($t_data as $v){

                $total_price += $v['price']*$v['number'];

            }

        }

        if(isset($param['export'])){

            $this->goods_push($data);

        }



        return view('', ['data' => $data,'total_price' => $total_price,'total_count' => $total_count,'goods_list' => $goods_list]);

    }



    /**

     * @param int $pa

     * @return array|mixed|\think\response\View

     * 礼包商品

     */

    public function gift($pa = 10)

    {



        $param = Request::instance()->param();

        $where = [];

        if (!empty($param['interval_timer'])) {

            $interval_timer = explode('/', $param['interval_timer']);

            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];

        }

        if (!empty($param['status'])){

            if($param['status'] == 2){

                $where['o.status'] = ['in', $this->status];

            }else{

                $where['o.status'] = ['eq', $param['status']];

            }

        }

        if (!empty($param['gift_id'])) $where['gift_id'] = ['eq', $param['gift_id']];

        if(!empty($param['title'])) $where['gift_id'] = ['eq',Db::name('gift')->where(['title' => ['like','%'.$param['title'].'%']])->value('id')];

        $data = (new Order())

            ->alias('o')

            ->where('order_type', 3)

            ->where($where)

            ->order('id', 'desc')

            ->paginate($pa, false, ['query' => $param])->each(function($item,$key){

                switch ($item->t_type){

                    case 1:

                    $item->t_type_text = '礼包';

                    break;

                    case 2:

                        $item->t_type_text = '会员卡';

                        $item->gift_id = $item->system_card_id;

                        $item->gift_title = $item->mc_title;

                        $item->gift_start_time = $item->mc_start_time;

                        $item->gift_end_time = $item->mc_end_time;

                        $item->sale_price = $item->amount;

                        break;

                }

            });

        $gift_list = (new Order())->alias('o')->join('gift g', 'o.gift_id = g.id','left')->group('g.id')->select();



        $total_count = (new Order())->alias('o')->where('order_type', 3)->where($where)->count();

        $total_price = (new Order())->alias('o')->where('order_type', 3)->where($where)->sum('sale_price');

        if(isset($param['export'])){

            $this->gift_push($data);

        }

        return view('', ['data' => $data, 'total_price' => $total_price, 'total_count' => $total_count, 'gift_list' => $gift_list]);

    }



    /**

     * @param int $pa

     * @return array|mixed|\think\response\View

     * 服务商品

     */

    public function service($pa = 10)

    {

        $param = Request::instance()->param();

        $where = [];

        if (!empty($param['interval_timer'])) {

            $interval_timer = explode('/', $param['interval_timer']);

            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];

        }

        if (!empty($param['status'])){

            if($param['status'] == 2){

                $where['o.status'] = ['in', $this->status];

            }else{

                $where['o.status'] = ['eq', $param['status']];

            }

        }

        if (!empty($param['single_parent_id'])) $where['single_parent_id'] = ['eq', $param['single_parent_id']];

        if(!empty($param['shop_id'])) $where['o.shop_id'] = ['eq',$param['shop_id']];

        if (!empty($param['title'])){

            $where['single_parent_id|single_id'] = ['eq', Db::name('single')->where('cat_name','like','%'.$param['title'].'%')->value('id')];

        }

        $data = (new Order())

            ->alias('o')

            ->join('single s','o.single_parent_id = s.id','left')

            ->join('shop sp','o.shop_id = sp.id','left')

            ->where('order_type', 2)

            ->where($where)

            ->order('o.id', 'desc')

            ->field('s.*,o.*,sp.shop_title')

            ->paginate($pa, false, ['query' => $param])->each(function($item,$key){

                $item->two_level = Db::name('single')->where('id',$item->single_id)->value('cat_name');

            });

        $single_list = (new Order())->alias('o')->join('single s', 'o.single_parent_id = s.id')->group('o.single_parent_id')->select();

        $total_count = (new Order())->alias('o')->where('order_type', 2)->where($where)->count();

        $total_price = (new Order())->alias('o')->where('order_type', 2)->where($where)->sum('amount');

        $sj_total_price = (new Order())->alias('o')->where('order_type', 2)->where($where)->sum('sj');

        if(isset($param['export'])){



            $this->service_push($data);

        }



        return view('', ['data' => $data, 'total_price' => $total_price, 'total_count' => $total_count, 'single_list' => $single_list,'sj_total_price' => $sj_total_price]);

    }





    public function order()

    {

        $param = Request::instance()->param();

        $data = Db::name('order')

            ->alias('o')

            ->where('shop_order_number', $param['shop_order_number'])

            ->paginate(10, false, ['query' => $param]);

        return view('', []);

    }



    public function daochu()

    {



        $param = Request::instance()->param();

        $level = \session('role_id');//当前登录的管理员界别 5 7 8 9 10

        $page = Request::instance()->param('page',10);

        $where = [];

        $auding_status = Session::get('auding_status');

        if($auding_status == 1){

            $where['shop_id'] = ['eq',\session('shop_id')];

        }





        if (!empty($param['status'])) $where['o.status'] = ['eq', $param['status']];

        if (!empty($param['shop_id'])) $where['o.shop_id'] = ['eq', $param['shop_id']];

        if (!empty($param['title'])) $where['m.username|o.shop_order_number|o.order_number'] = ['eq', $param['title']];



        $end_time = time();

        if (!empty($param['interval_timer'])) {

            $interval_timer = explode('/', $param['interval_timer']);

            $end_time = strtotime($interval_timer[1]);

            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];

        }





        $data = \app\console\model\Order::alias('o')

            ->join('order_goods og','o.id = og.order_id','left')

            ->join('member m', 'o.member_id = m.id','left')

            ->join('shop s', 'o.shop_id = s.id','left')

            ->field('o.order_number,o.gift_id,o.shop_id,DATE_FORMAT(o.create_time,"%Y/%m/%d") create_time,o.id,o.order_type,o.single_parent_id,o.single_id,og.goods_id,o.amount,o.shop_order_number,m.username,o.status,o.refund_create_time,s.shop_title,o.shipping,o.is_read,o.order_number')

            ->where($where)

            ->fetchSql(false)

            ->order('o.id','desc')

            ->paginate(10000, false, ['query' => $param])->each(function($item,$key) use($level,$end_time){

                if($item['status'] == 12 || $item['status'] == 10 || $item['status'] == 11){

                    $item->refund_create_time = Db::name('order_refund')->where('order_id',$item['id'])->value('create_time');

                }

                $item->year = explode('/',$item->create_time)[0];

                $item->month = explode('/',$item->create_time)[1];

                $item->pzrq = date('Y/m/d',$end_time);//凭证日期

                $item->kjnd = date('Y',$end_time);//会计年度

                $item->kjqj = date('m',$end_time);//会计期间

                $item->pzz = '记';//凭证字

                $item->pzh = $key;//凭证号

                $item->d = '';

                $item->c = '';

                $item->pzzy = '';

                switch ($item->order_type){

                    case 1;

                        $dc = Db::name('shop_goods')->where('id',$item->goods_id)->value('dc');

                        $title = Db::name('shop_goods')->where('id',$item->goods_id)->value('title');

                        $item->pzzy = $title;

                        if($dc == 1){

                            $item->d = $item->amount;

                        }else{

                            $item->c = $item->amount;

                        }

                        $item->subject_code = Db::name('shop_goods')->where('id',$item->goods_id)->value('code');

                        break;

                    case 2;

                        $dc = Db::name('single')->where('id',$item->single_id)->value('dc');

                        $title = Db::name('single')->where('id',$item->single_id)->value('cat_name');

                        $item->pzzy = $title;

                        if($dc == 1){

                            $item->d = $item->amount;

                        }else{

                            $item->c = $item->amount;

                        }

                        $item->subject_code = Db::name('single')->where('id',$item->single_parent_id)->value('code').'.'.Db::name('single')->where('id',$item->single_id)->value('code');

                        break;

                    case 3;

                        $dc = Db::name('gift')->where('id',$item->gift_id)->value('dc');

                        $title = Db::name('gift')->where('id',$item->gift_id)->value('title');

                        $item->pzzy = $title;

                        if($dc == 1){

                            $item->d = $item->amount;

                        }else{

                            $item->c = $item->amount;

                        }

                        $item->subject_code = Db::name('gift')->where('id',$item->gift_id)->value('code');

                        break;

                }

                $item->subject_name = '';//科目名称

                $item->bbdm = 'RMB';//币别代码

                $item->bbmc = '人民币';//币别代码

                $item->amount = $item->amount;//原币金额

                $item->zd = '';//制单

                $item->sh = '';//审核

                $item->hz = 'NONE';//核准

                $item->cn = 'NONE';//出纳

                $item->jb = '';//经办

                $item->jsfs = '*';//结算方式

                $item->jsh = '';//结算号

                $item->sl = '0';//数量

                $item->sldw = '*';//数量单位

                $item->dj = 0;//单价

                $item->ckxx = '';//参考信息

                $item->ywrq = '';//业务日期  ....

                $item->wlyybh = '';//往来业务编号

                $item->ffs = 0;//附件数

                $item->xh = 1 ;//序号

                $item->xtmk = '';//系统模块

                $item->ywms = '';//业务描述

                $item->hl = 1;//汇率

                $item->flxh = '';//分录序号

                $item->hsxm = '';//合算项目

                $item->gz = 0;//过账

                $item->jzpz = '';//机制凭证

                $item->xjll = '';//现金流量

                $item->order_number = '';//现金流量



            });



        if(isset($param['export'])){



            $this->push($data);

        }



    }



    public function push($data, $name = 'KingDee')

    {



        set_time_limit(0);



        ini_set('memory_limit', '1024M');



        error_reporting(E_ALL);



        date_default_timezone_set('Asia/Shanghai');







        import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');



        $objPHPExcel = new \PHPExcel();







        /*以下是一些设置 ，什么作者  标题啊之类的*/

        $objPHPExcel->getActiveSheet(0)->getDefaultRowDimension()->setRowHeight(12);

        $objPHPExcel->getProperties()->setCreator("管理员")



            ->setLastModifiedBy("管理员")



            ->setTitle("电话本导出")



            ->setSubject("数据EXCEL导出")



            ->setDescription("备份数据")



            ->setKeywords("excel")



            ->setCategory("result file");

        $objPHPExcel->setActiveSheetIndex()



            //Excel的第A列，shop_name是你查出数组的键值，下面以此类推

//            ->setCellValue('A1', '凭证日期')

//            ->setCellValue('B1', '会计年度')

//            ->setCellValue('C1', '会计期间')

//            ->setCellValue('D1', '凭证字')

//            ->setCellValue('E1', '凭证号')

//            ->setCellValue('F1', '科目代码')

//            ->setCellValue('G1', '科目名称')

//            ->setCellValue('H1', '币别代码')

//            ->setCellValue('I1', '币别名称')

//            ->setCellValue('J1', '原币金额')

//            ->setCellValue('K1', '借方')

//            ->setCellValue('L1', '贷方')

//            ->setCellValue('M1', '制单')

//            ->setCellValue('N1', '审核')

//            ->setCellValue('O1', '核准')

//            ->setCellValue('P1', '出纳')

//            ->setCellValue('Q1', '经办')

//            ->setCellValue('R1', '结算方式')

//            ->setCellValue('S1', '结算号')

//            ->setCellValue('T1', '凭证摘要')

//            ->setCellValue('U1', '数量')

//            ->setCellValue('V1', '数量单位')

//            ->setCellValue('W1', '单价')

//            ->setCellValue('X1', '参考信息')

//            ->setCellValue('Y1', '业务日期')

//            ->setCellValue('Z1', '往来业务编号')

//            ->setCellValue('AA1', '附件数')

//            ->setCellValue('AB1', '序号')

//            ->setCellValue('AC1', '系统模块')

//            ->setCellValue('AD1', '业务描述')

//            ->setCellValue('AE1', '汇率')

//            ->setCellValue('AF1', '分录序号')

//            ->setCellValue('AG1', '核算项目')

//            ->setCellValue('AH1', '过账')

//            ->setCellValue('AI1', '机制凭证')

//            ->setCellValue('AJ1', '现金流量')



            ->setCellValue('A1', 'FDate')

            ->setCellValue('B1', 'FYear')

            ->setCellValue('C1', 'FPeriod')

            ->setCellValue('D1', 'FGroupID')

            ->setCellValue('E1', 'FNumber')

            ->setCellValue('F1', 'FAccountNum')

            ->setCellValue('G1', 'FAccountName')

            ->setCellValue('H1', 'FCurrencyNum')

            ->setCellValue('I1', 'FCurrencyName')

            ->setCellValue('J1', 'FAmountFor')

            ->setCellValue('K1', 'FDebit')

            ->setCellValue('L1', 'FCredit')

            ->setCellValue('M1', 'FPreparerID')

            ->setCellValue('N1', 'FCheckerID')

            ->setCellValue('O1', 'FApproveID')

            ->setCellValue('P1', 'FCashierID')

            ->setCellValue('Q1', 'FHandler')

            ->setCellValue('R1', 'FSettleTypeID')

            ->setCellValue('S1', 'FSettleNo')

            ->setCellValue('T1', 'FExplanation')

            ->setCellValue('U1', 'FQuantity')

            ->setCellValue('V1', 'FMeasureUnitID')

            ->setCellValue('W1', 'FUnitPrice')

            ->setCellValue('X1', 'FReference')

            ->setCellValue('Y1', 'FTransDate')

            ->setCellValue('Z1', 'FTransNo')

            ->setCellValue('AA1', 'FAttachments')

            ->setCellValue('AB1', 'FSerialNum')

            ->setCellValue('AC1', 'FObjectName')

            ->setCellValue('AD1', 'FParameter')

            ->setCellValue('AE1', 'FExchangeRate')

            ->setCellValue('AF1', 'FEntryID')

            ->setCellValue('AG1', 'FItem')

            ->setCellValue('AH1', 'FPosted')

            ->setCellValue('AI1', 'FInternalInd')

            ->setCellValue('AJ1', 'FCashFlow')

//            ->setCellValue('AK1', '订单编号')

//            ->setCellValue('AL1', '商品其他信息')

        ;

        //



        $sheet1 = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ'];



        $total = count($data);

        for ($a = 1; $a <= $total+1; $a++) {

            for ($x = 0; $x < count($sheet1); $x++) {

                $objPHPExcel->getActiveSheet(0)->getStyle($sheet1[$x] . $a)->applyFromArray(

                    array(

                        'font' => array(

                            'bold' => false,

                            'size' => 10,

                            'name' => '宋体',

                        ),



                        'alignment' => array(

                            'horizontal' =>

                                \PHPExcel_Style_Alignment::HORIZONTAL_LEFT



                        )



                    )



                );



            }

        }









        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/







        $n = 0;



        foreach ($data as $v) {



            $num = $n + 2;

            $objPHPExcel->getactivesheet()



                //Excel的第A列，shop_name是你查出数组的键值，下面以此类推

                ->setCellValue('A' . $num, $v['pzrq'])

                ->setCellValue('B' . $num, $v['kjnd'])

                ->setCellValue('C' . $num, $v['kjqj'])

                ->setCellValue('D' . $num, $v['pzz'])

                ->setCellValue('E' . $num, $v['pzh'])

                ->setCellValue('F' . $num, $v['subject_code'])

                ->setCellValue('G' . $num, $v['subject_name'])

                ->setCellValue('H' . $num, $v['bbdm'])

                ->setCellValue('I' . $num, $v['bbmc'])

                ->setCellValue('J' . $num, $v['amount'])

                ->setCellValue('K' . $num, $v['d'])

                ->setCellValue('L' . $num, $v['c'])

                ->setCellValue('M' . $num, $v['zd'])

                ->setCellValue('N' . $num, $v['sh'])

                ->setCellValue('O' . $num, $v['hz'])

                ->setCellValue('P' . $num, $v['cn'])

                ->setCellValue('Q' . $num, $v['jb'])

                ->setCellValue('R' . $num, $v['jsfs'])

                ->setCellValue('S' . $num, $v['jsh'])

                ->setCellValue('T' . $num, $v['pzzy'])

                ->setCellValue('U' . $num, $v['sl'])

                ->setCellValue('V' . $num, $v['sldw'])

                ->setCellValue('W' . $num, $v['dj'])

                ->setCellValue('X' . $num, $v['ckxx'])

                ->setCellValue('Y' . $num, $v['ywrq'])

                ->setCellValue('Z' . $num, $v['wlyybh'])

                ->setCellValue('AA' . $num, $v['ffs'])

                ->setCellValue('AB' . $num, $v['xh'])

                ->setCellValue('AC' . $num, $v['xtmk'])

                ->setCellValue('AD' . $num, $v['ywms'])

                ->setCellValue('AE' . $num, $v['hl'])

                ->setCellValue('AF' . $num, $v['flxh'])

                ->setCellValue('AG' . $num, $v['hsxm'])

                ->setCellValue('AH' . $num, $v['gz'])

                ->setCellValue('AI' . $num, $v['jzpz'])

                ->setCellValue('AJ' . $num, $v['xjll'])

//                ->setCellValue('AK' . $num, $v['order_number'])

//                ->setCellValue('AL' . $num, $v['others'])



            ;



            $n++;



        }



        $objPHPExcel->getActiveSheet(0)->setTitle('Page1');

        $objPHPExcel->createSheet();

        $objPHPExcel->setActiveSheetIndex(1);

        $objPHPExcel->getActiveSheet()->setTitle('t_Schema');

        $arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');

        $j = 1;

        foreach (cache('excel_data') as $kk => $vv){

            $objPHPExcel->getactivesheet()->setCellValue('A' . $j, $vv[0])

                ->setCellValue('B' . $j, $vv['1'])

                ->setCellValue('C' . $j, $vv['2'])

                ->setCellValue('D' . $j, $vv['3'])

                ->setCellValue('E' . $j, $vv['4'])

                ->setCellValue('F' . $j, $vv['5'])

                ->setCellValue('G' . $j, $vv['6'])

                ->setCellValue('H' . $j, $vv['7'])

                ->setCellValue('I' . $j, $vv['8'])

                ->setCellValue('J' . $j, $vv['9'])

                ->setCellValue('K' . $j, $vv['10'])

                ->setCellValue('L' . $j, $vv['11'])

                ->setCellValue('M' . $j, $vv['12'])

                ->setCellValue('N' . $j, $vv['13'])

                ->setCellValue('O' . $j, $vv['14'])

                ->setCellValue('P' . $j, $vv['15'])

                ->setCellValue('Q' . $j, $vv['16'])

                ->setCellValue('R' . $j, $vv['17'])

                ->setCellValue('S' . $j, $vv['18'])

                ->setCellValue('T' . $j, $vv['19']);

            $j++;

        }



        $sheet1 = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ'];

        //$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('20');



        $objPHPExcel->getActiveSheet(1)->getDefaultRowDimension()->setRowHeight(12);



        $total_2 = count(session('excel_data'));

        for ($aa = 1; $aa <= $total_2; $aa++) {



            for ($xx = 0; $xx < count($sheet1); $xx++) {

                $objPHPExcel->getActiveSheet()->getStyle($sheet1[$xx] . $aa)->applyFromArray(

                    array(

                        'font' => array(

                            'bold' => false,

                            'size' => 10,

                            'name' => '宋体',

                            'height' => 30

                        ),

                        'alignment' => array(

                            'horizontal' =>

                                \PHPExcel_Style_Alignment::HORIZONTAL_LEFT



                        )



                    )



                );



            }

        }





        $objPHPExcel->setActiveSheetIndex(0);



        ob_end_clean();



//        header('Content-Disposition: attachment;charset=utf-8;filename="' . $name . '.xls"');

//        header('Cache-Control: max-age=0');

//        header('Content-Type:application/vnd.ms-excel');

//        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');



        header('Content-Disposition: attachment;charset=gb2312;filename="' . $name . '.xlsx"');

        header('Cache-Control: max-age=0');

        header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        //设置保存版本格式

//        if($excel2007){

//            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

//            $filename = $filename.'.xlsx';

//        }else{

//            $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

//            $filename = $filename.'.xls';

//        }

//        ---------------------

//作者：weixin_39696369

//来源：CSDN

//原文：https://blog.csdn.net/weixin_39696369/article/details/78646494

//版权声明：本文为博主原创文章，转载请附上博文链接！

        //xlsx Excel2007 xls

        //需要导出xls的话，用 header('Content-Type:application/vnd.ms-excel');

        //需要导出xlsx的话，用 header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        //xls和xlsx的区别的话，是储存数据量大小的问题，xls只可以保存大概5、6w数据，xlsx可以100w左右



        $objWriter->save('php://output');

        exit;

    }





    /**

     * 导入EXCEL

     */

    public function excelToArray()

    {

        // dirname(__FILE__) .

        $rule = require_once '../extend/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php';



        // import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');



        //$objPHPExcel = new \PHPExcel();

        //加载excel文件

        $filename = './KingDee (123).xlsx';



        $objPHPExcelReader = \PHPExcel_IOFactory::load($filename);

        $sheet = $objPHPExcelReader->getSheet(0);        // 读取第一个工作表(编号从 0 开始)

        $highestRow = $sheet->getHighestRow();           // 取得总行数

        $highestColumn = $sheet->getHighestColumn();     // 取得总列数



        $arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');

        // 一次读取一列

        $res_arr = array();

        for ($row = 1; $row <= $highestRow; $row++) {

            $row_arr = array();

            for ($column = 0; $arr[$column] != 'U'; $column++) {

                $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();

                $row_arr[] = $val;

            }



            $res_arr[] = $row_arr;

        }

        halt($res_arr);

        Cache::set('excel_data', $res_arr);



        //return $res_arr;



    }





    public function goods_push($data, $name = '商品导出')

    {



        set_time_limit(0);

        ini_set('memory_limit', '1024M');

        error_reporting(E_ALL);

        date_default_timezone_set('Asia/Shanghai');



        import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');

        $objPHPExcel = new \PHPExcel();







        /*以下是一些设置 ，什么作者  标题啊之类的*/



        $objPHPExcel->getProperties()->setCreator("管理员")



            ->setLastModifiedBy("管理员")



            ->setTitle("电话本导出")



            ->setSubject("数据EXCEL导出")



            ->setDescription("备份数据")



            ->setKeywords("excel")



            ->setCategory("result file");



        $objPHPExcel->setActiveSheetIndex(0)



            //Excel的第A列，shop_name是你查出数组的键值，下面以此类推

            ->setCellValue('A1', '订单号')

            ->setCellValue('B1', '门店名称')

            ->setCellValue('C1', '商品ID')

            ->setCellValue('D1', '商品标题')

            ->setCellValue('E1', '商品价格')

            ->setCellValue('F1', '商品属性')

            ->setCellValue('G1', '商品数量')

            ->setCellValue('H1', '创建日期')



        ;



        $sheet1 = [];





        for ($x = 0; $x < count($sheet1); $x++) {



            $objPHPExcel->getActiveSheet()->getStyle($sheet1[$x] . '1')->applyFromArray(



                array(



                    'font'      => array(



                        'bold' => true



                    ),



                    'alignment' => array(



                        'horizontal' =>



                            \PHPExcel_Style_Alignment::HORIZONTAL_CENTER



                    )



                )



            );



        }







        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/







        $n = 0;



        foreach ($data as $v) {



            $num = $n + 2;

            $objPHPExcel->setActiveSheetIndex(0)



                //Excel的第A列，shop_name是你查出数组的键值，下面以此类推

                ->setCellValue('A' . $num, $v['shop_order_number'])

                ->setCellValue('B' . $num, $v['shop_title'])

                ->setCellValue('C' . $num, $v['goods_id'])

                ->setCellValue('D' . $num, $v['title'])

                ->setCellValue('E' . $num, $v['price'])

                ->setCellValue('F' . $num, $v['attribute'])

                ->setCellValue('G' . $num, $v['number'])

                ->setCellValue('H' . $num, $v['create_time'])



            ;







            $n++;



        }







        $objPHPExcel->getActiveSheet(0)->setTitle('商品导出');



        $objPHPExcel->setActiveSheetIndex(0);







        ob_end_clean();



        header('Content-Type: application/vnd.ms-excel');



        header('Content-Disposition: attachment;charset=utf-8;filename="' . $name . '.xls"');



        header('Cache-Control: max-age=0');



        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');



        $objWriter->save('php://output');

        exit;

    }



    public function gift_push($data, $name = '礼包导出')

    {



        set_time_limit(0);

        ini_set('memory_limit', '1024M');

        error_reporting(E_ALL);

        date_default_timezone_set('Asia/Shanghai');



        import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');

        $objPHPExcel = new \PHPExcel();





        /*以下是一些设置 ，什么作者  标题啊之类的*/

        $objPHPExcel->getProperties()->setCreator("管理员")

            ->setLastModifiedBy("管理员")

            ->setTitle("电话本导出")

            ->setSubject("数据EXCEL导出")

            ->setDescription("备份数据")

            ->setKeywords("excel")

            ->setCategory("result file");

        $objPHPExcel->setActiveSheetIndex(0)

            //Excel的第A列，shop_name是你查出数组的键值，下面以此类推

            ->setCellValue('A1', '订单号')

            ->setCellValue('B1', '礼包ID')

            ->setCellValue('C1', '礼包标题')

            ->setCellValue('D1', '开始时间')

            ->setCellValue('E1', '结束时间')

            ->setCellValue('F1', '售卖价格')

            ->setCellValue('G1', '创建时间')



        ;



        $sheet1 = [];





        for ($x = 0; $x < count($sheet1); $x++) {



            $objPHPExcel->getActiveSheet()->getStyle($sheet1[$x] . '1')->applyFromArray(



                array(



                    'font'      => array(



                        'bold' => true



                    ),



                    'alignment' => array(



                        'horizontal' =>



                            \PHPExcel_Style_Alignment::HORIZONTAL_CENTER



                    )



                )



            );



        }







        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/







        $n = 0;



        foreach ($data as $v) {



            $num = $n + 2;

            $objPHPExcel->setActiveSheetIndex(0)



                //Excel的第A列，shop_name是你查出数组的键值，下面以此类推

                ->setCellValue('A' . $num, $v['order_number'])

                ->setCellValue('B' . $num, $v['gift_id'])

                ->setCellValue('C' . $num, $v['gift_title'])

                ->setCellValue('D' . $num, $v['gift_start_time'])

                ->setCellValue('E' . $num, $v['gift_end_time'])

                ->setCellValue('F' . $num, $v['sale_price'])

                ->setCellValue('G' . $num, $v['create_time'])



            ;







            $n++;



        }







        $objPHPExcel->getActiveSheet(0)->setTitle('礼包导出');



        $objPHPExcel->setActiveSheetIndex(0);







        ob_end_clean();



        header('Content-Type: application/vnd.ms-excel');



        header('Content-Disposition: attachment;charset=utf-8;filename="' . $name . '.xls"');



        header('Cache-Control: max-age=0');



        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');



        $objWriter->save('php://output');

        exit;

    }



    public function service_push($data, $name = '服务导出')

    {



        set_time_limit(0);

        ini_set('memory_limit', '1024M');

        error_reporting(E_ALL);

        date_default_timezone_set('Asia/Shanghai');



        import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');

        $objPHPExcel = new \PHPExcel();





        /*以下是一些设置 ，什么作者  标题啊之类的*/

        $objPHPExcel->getProperties()->setCreator("管理员")

            ->setLastModifiedBy("管理员")

            ->setTitle("电话本导出")

            ->setSubject("数据EXCEL导出")

            ->setDescription("备份数据")

            ->setKeywords("excel")

            ->setCategory("result file");

        $objPHPExcel->setActiveSheetIndex(0)

            //Excel的第A列，shop_name是你查出数组的键值，下面以此类推

            ->setCellValue('A1', '订单号')

            ->setCellValue('B1', '门店名称')

            ->setCellValue('C1', '服务一级类')

            ->setCellValue('D1', '服务二级类')

            ->setCellValue('E1', '订单总额')

            ->setCellValue('F1', '运费金额')

            ->setCellValue('G1', '创建日期')

        ;



        $sheet1 = [];





        for ($x = 0; $x < count($sheet1); $x++) {



            $objPHPExcel->getActiveSheet()->getStyle($sheet1[$x] . '1')->applyFromArray(



                array(



                    'font'      => array(



                        'bold' => true



                    ),



                    'alignment' => array(



                        'horizontal' =>



                            \PHPExcel_Style_Alignment::HORIZONTAL_CENTER



                    )



                )



            );



        }







        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/







        $n = 0;



        foreach ($data as $v) {



            $num = $n + 2;

            $objPHPExcel->setActiveSheetIndex(0)



                //Excel的第A列，shop_name是你查出数组的键值，下面以此类推

                ->setCellValue('A' . $num, $v['order_number'])

                ->setCellValue('B' . $num, $v['shop_title'])

                ->setCellValue('C' . $num, $v['cat_name'])

                ->setCellValue('D' . $num, $v['two_level'])

                ->setCellValue('E' . $num, $v['amount'])

                ->setCellValue('F' . $num, $v['freight'])

                ->setCellValue('G' . $num, $v['create_time'])



            ;







            $n++;



        }







        $objPHPExcel->getActiveSheet(0)->setTitle('服务导出');



        $objPHPExcel->setActiveSheetIndex(0);







        ob_end_clean();



        header('Content-Type: application/vnd.ms-excel');



        header('Content-Disposition: attachment;charset=utf-8;filename="' . $name . '.xls"');



        header('Cache-Control: max-age=0');



        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');



        $objWriter->save('php://output');

        exit;

    }

}